<?php
/**
 * GioCMS
 * 
 * LICENSE
 *
 * This source file is subject to the GNU GENERAL PUBLIC LICENSE Version 2 
 * that is bundled with this package in the file LICENSE.txt.
 * It is also available through the world-wide-web at this URL:
 * http://www.gnu.org/licenses/gpl-2.0.txt
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to license@ninhgio.com so we can send you a copy immediately.
 * 
 * @copyright	Copyright (c) 2010-2011 GioCMS (http://cms.ninhgio.com)
 * @license		http://www.gnu.org/licenses/gpl-2.0.txt GNU GENERAL PUBLIC LICENSE Version 2
 * @author		NinhGio - ninhgio@gmail.com
 * @since		1.0
 */

class Modules_News_Models_Mysql_Category extends Modules_News_Models_Category
{	
	public function getById($id)
	{
		$this->_conn = Gio_Db_Connection::getConnection();
		
		$sql = 'SELECT * FROM ' . $this->_conn->_tablePrefix . 'news_category 
							WHERE category_id = "' . $this->_conn->escape($id) . '" LIMIT 1';
		$rs = $this->_conn->query($sql);
		$return = (0 == mysql_num_rows($rs)) ? null : $this->_conn->fetchAll($rs);
		return $return;
	}
	
	public function getParent($categoryId) {
		$sql = 'SELECT * FROM ' . $this->_conn->_tablePrefix . 'news_category 
							WHERE root_id = "' . $this->_conn->escape($categoryId) . '" LIMIT 1';
		$rs = $this->_conn->query($sql);
		$return = (0 == mysql_num_rows($rs)) ? null : $this->_conn->fetchAll($rs);
		return $return;
	}
	
	public function getSubCategories($categoryId)
	{
		$this->_conn = Gio_Db_Connection::getConnection();
		
		$sql = 'SELECT * FROM ' . $this->_conn->_tablePrefix . 'news_category 
					WHERE status = "active" AND parent_id = ' . $this->_conn->escape($categoryId);
		$sql .= ' ORDER BY position ASC';
		$rs = $this->_conn->query($sql);
		$categories = array();
		while ($row = $this->_conn->fetchAll($rs)) {
			$categories[] = $row;
		}
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $categories;
	}

	public function getRootCategories($menuVisible = null)
	{
		$this->_conn = Gio_Db_Connection::getConnection();
		
		$sql = 'SELECT * FROM ' . $this->_conn->_tablePrefix . 'news_category 
					WHERE status = "active" AND parent_id = 0';
		if ($menuVisible === false) {
			$sql .= ' AND menu_visible = 0';
		} elseif ($menuVisible == true) {
			$sql .= ' AND menu_visible = 1';			
		}
		$rs = $this->_conn->query($sql);
		$categories = array();
		while ($row = $this->_conn->fetchAll($rs)) {
			$categories[] = $row;
		}
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $categories;
	}
	
	public function getHomeCategories($subCategory = false, $parentId = 0) 
	{
		$this->_conn = Gio_Db_Connection::getConnection();
		
		$sql = 'SELECT * FROM ' . $this->_conn->_tablePrefix . 'news_category 
					WHERE home_visible = 1';
		if ($subCategory) {
			$sql .= ' AND parent_id != 0';
		}
		if ($parentId >= 0) {
			$sql .= ' AND parent_id = ' . $parentId;
		}
		$sql .= ' ORDER BY position ASC';
		
		$rs = $this->_conn->query($sql);
		$categories = array();
		while ($row = $this->_conn->fetchAll($rs)) {
			$categories[] = $row;
		}
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $categories;
	}
	
	public function add($category)
	{
		$this->_conn = Gio_Db_Connection::getConnection();
		
		return $this->_conn->insert($category, $this->_conn->_tablePrefix . 'news_category');	
	}
	
	public function find($condition = array(), $offset = null, $limit = null)
	{
		$this->_conn = Gio_Db_Connection::getConnection();
		
		$sql = 'SELECT * FROM ' . $this->_conn->_tablePrefix . 'news_category AS c ORDER BY c.position ASC';
		$rs = $this->_conn->query($sql);
		$categories = array();
		while ($row = $this->_conn->fetchAll($rs)) {
			$categories[] = $row;
		}
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $categories;
	}
	
	public function count($condition = array())
	{
		$this->_conn = Gio_Db_Connection::getConnection();
		
		$sql = 'SELECT COUNT(*) as num_categories FROM ' . $this->_conn->_tablePrefix . 'news_category';
		$rs = $this->_conn->query($sql);
		$row = $this->_conn->fetchAll($rs);
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $row['num_categories'];
	}
	
	public function update($category)
	{
		$this->_conn = Gio_Db_Connection::getConnection();
		
		$sql = 'UPDATE ' . $this->_conn->_tablePrefix . 'news_category 
					SET name = "%s", slug = "%s", status = "%s", parent_id = "%s",
						home_visible = "%s", menu_visible = "%s", path = "%s", file_url = "%s",
						url = "%s"  
					WHERE category_id = "%d"';
		$sql = sprintf($sql, $this->_conn->escape($category['name']),
							$this->_conn->escape($category['slug']),
							$this->_conn->escape($category['status']),
							$this->_conn->escape($category['parent_id']),
							$this->_conn->escape($category['home_visible']),
							$this->_conn->escape($category['menu_visible']),
							$this->_conn->escape($category['path']),
							$this->_conn->escape($category['file_url']),
							$this->_conn->escape($category['url']),
							$this->_conn->escape($category['category_id']));
		$rs = $this->_conn->query($sql);
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $this->_conn->affectedRows();	
	}
	
	public function delete($categoryId)
	{
		$this->_conn = Gio_Db_Connection::getConnection();
		
		$sql = 'DELETE FROM ' . $this->_conn->_tablePrefix . 'news_category 
							WHERE category_id = ' . $this->_conn->escape($categoryId)
							. ' OR parent_id = ' . $this->_conn->escape($categoryId);
		$rs = $this->_conn->query($sql);
		$affectRow = $this->_conn->affectedRows();
		$this->_conn->freeResult($rs);
		return $affectRow;
	}
	
	public function treeCategories($parentId = 0, $depth = 0, $active = false, $menuVisible = false, $homeVisible = false)
	{
		$this->_conn = Gio_Db_Connection::getConnection();
		
		$sql = 'SELECT * FROM ' . $this->_conn->_tablePrefix . 'news_category AS c
					WHERE c.parent_id = ' . $this->_conn->escape($parentId);
		if ($active) {
			$sql .= ' AND c.status = "active"';	
		}
		if ($menuVisible) {
			$sql .= ' AND c.menu_visible = 1';	
		}
		if ($homeVisible) {
			$sql .= ' AND c.home_visible = 1';	
		}
		$sql .= ' ORDER BY c.position ASC, c.name ASC';
		$rs = $this->_conn->query($sql);
		$categories = array();
		while ($row = $this->_conn->fetchAll($rs)) {
			$row['depth'] = $depth;
			$index = count($categories);
			$categories[$index] = $row;
			$newDepth = $depth + 1;
			$childCategories = $this->treeCategories($row['category_id'], $newDepth, $active, $menuVisible, $homeVisible);
			if ($childCategories != null) {
				$categories[$index]['has_child'] = true;
				foreach ($childCategories as $index => $childCategory) {
					$categories[] = $childCategory;
				}
			}
		}
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $categories; 
	}
	
	public function updatePosition($categoryId, $position) 
	{
		$this->_conn = Gio_Db_Connection::getConnection();
		
		$sql = 'UPDATE ' . $this->_conn->_tablePrefix . 'news_category 
					SET position = "%s" 
					WHERE category_id = "%s"';
		$sql = sprintf($sql, $this->_conn->escape($position),
							$this->_conn->escape($categoryId));
		$rs = $this->_conn->query($sql);
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $this->_conn->affectedRows();
	}
	
	public function updateStatus($categoryId, $status)
	{
		$this->_conn = Gio_Db_Connection::getConnection();
		
		$sql = 'UPDATE ' . $this->_conn->_tablePrefix . 'news_category 
					SET status = "%s" 
					WHERE category_id = "%s"';
		$sql = sprintf($sql, $this->_conn->escape($status),
							$this->_conn->escape($categoryId));
		$rs = $this->_conn->query($sql);
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $this->_conn->affectedRows();
	}
}